

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwInfoEdProgressSummaryReport]'))
DROP VIEW [dbo].[vwInfoEdProgressSummaryReport]
GO
/****** Object:  View [dbo].[vwInfoEdProgressSummaryReport]    Script Date: 05/30/2008 17:11:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =======================================================================  
-- Object Name: (VIEW) dbo.vwInfoEdProgressSummaryReport
--  
-- Author:      Ponraja Chandran 
--  
-- Create date: 11/19/2008   
--  
-- Description: View to Progress Report Deliverables  
--  
-- Used BY Procedure/Report:   
--  
--------------------------------------------------------------------------  
-- Date        Initials  Modification  
--------------------------------------------------------------------------  
-- 11//08    PC Created based on vwInfoEdProgressSummaryReport
--  
-- =======================================================================  
CREATE VIEW [dbo].[vwInfoEdProgressSummaryReport]  
AS  
        
SELECT    D.prop_no,  
          D.ReportId,  
          D.ReportName,  
          RTRIM(C1.code_desc) AS REPORTCAT,  
          SUBSTRING(D.REPORTCAT,LEN('PARTNERS_P_')+1,100) AS REPORTCAT_CODE,  
          D.REPORTCAT AS REPORTCAT_CODE_LONG,  
          C2.code_desc AS REPORTTYPE,  
          D.SubmittedDate,  
          D.SubmittedByLastName + ', ' + D.SubmittedByFirstName AS SubmittedBy,  
--          FE.FullyExecutedDate AS CompletedDate,  
--          FE.CompletedBy,  
--          UEAR.UnExecutedAgreementRecdDate,  
--          UEAR.AgrReceivedBy,  
--          CASE WHEN dbo.fnRemoveTime(S.ProcessedDate) <= '1/1/2009'  
--               THEN 'N/A'            
--               ELSE CONVERT(VARCHAR(5),DATEDIFF(DD,UEAR.UnExecutedAgreementRecdDate,FE.FullyExecutedDate))  
--          END AS DaysComplCalc,  
          D.DueDate,           
          RTRIM(C3.code_desc) AS REPORTSTAT, --Commenting out above code until we figure out where the business rule came from  
          SUBSTRING(S.REPORTSTAT,LEN('PARTNERS_P_')+1,100) AS REPORTSTAT_CODE,  
          S.REPORTSTAT AS REPORTSTAT_CODE_LONG,  
          S.StatusDate,  
          S.StatusByLastName + ', '+ S.StatusByFirstName AS StatusBy,  
          -- For SubContracts, FolderNumber should be ReportName...  
          CASE WHEN D.REPORTCAT = 'PARTNERS_P_REPORTCAT_34'   
               THEN D.ReportName + ': ' + S.Comments  
               ELSE S.Comments  
          END AS Comments,  
          CASE WHEN D.SubmittedDate IS NOT NULL  
               THEN 1  
               ELSE 0  
          END AS ConvertedInd,  
          D.DeliverableID,  
          S.ProcessedDate,  
          D.AccountNo,  
          D.PeriodStartDate,  
          D.PeriodEndDate  
            
FROM      dbo.InfoEdPTDeliverable D LEFT OUTER JOIN dbo.InfoEdCodetab C1 ON D.REPORTCAT = C1.codeID  
               LEFT OUTER JOIN dbo.InfoEdCodetab C2 ON D.REPORTTYPE = C2.codeID  
               LEFT OUTER JOIN dbo.InfoEdPTDeliverableStatus S ON D.DeliverableID = S.DeliverableID  
               LEFT OUTER JOIN dbo.InfoEdCodetab C3 ON S.REPORTSTAT = C3.codeID  
--               -- Get FullyExecuted Date for Completed Date (Last Date as FullyExecuted)  
--               LEFT OUTER JOIN  
--                    (SELECT   D.ReportID,  
--                              S.ProcessedDate AS FullyExecutedDate,  
--                              S.StatusByLastName + ', ' + S.StatusByFirstName AS CompletedBy  
--                    FROM      dbo.InfoEdPTDeliverable D   
--                                   LEFT OUTER JOIN dbo.InfoEdPTDeliverableStatus S ON D.DeliverableID = S.DeliverableID           
--                         INNER JOIN   
--                              (SELECT     D.ReportId,  
--                                          MAX(S.ProcessedDate) AS FullyExecutedDate  
--                                FROM      dbo.InfoEdPTDeliverable D   
--                                               LEFT OUTER JOIN dbo.InfoEdPTDeliverableStatus S ON D.DeliverableID = S.DeliverableID           
--                                WHERE     S.REPORTSTAT in ('PARTNERS_P_REPORTSTAT_22','PARTNERS_P_REPORTSTAT_31')  
--                                GROUP BY D.ReportId)  C ON D.ReportID = C.ReportID AND S.ProcessedDate = C.FullyExecutedDate  
--                    WHERE     S.REPORTSTAT in ('PARTNERS_P_REPORTSTAT_22','PARTNERS_P_REPORTSTAT_31') ) FE ON D.ReportId = FE.ReportId  
--  
--               -- Get UnExecutedAgreementRecd Date (First Date) for DaysComplCalc (40,41 codes)  
--               LEFT OUTER JOIN  
--                    (SELECT   D.ReportID,  
--                              S.ProcessedDate AS UnExecutedAgreementRecdDate,  
--                              S.StatusByLastName + ', ' + S.StatusByFirstName AS AgrReceivedBy  
--                    FROM      dbo.InfoEdPTDeliverable D   
--                                   LEFT OUTER JOIN dbo.InfoEdPTDeliverableStatus S ON D.DeliverableID = S.DeliverableID           
--                         INNER JOIN   
--                              (SELECT     D.ReportId,  
--                                          MIN(S.ProcessedDate) AS UnExecutedAgreementRecdDate  
--                                FROM      dbo.InfoEdPTDeliverable D   
--                                               LEFT OUTER JOIN dbo.InfoEdPTDeliverableStatus S ON D.DeliverableID = S.DeliverableID           
--                                WHERE     S.REPORTSTAT in ('PARTNERS_P_REPORTSTAT_40','PARTNERS_P_REPORTSTAT_41')  
--                                GROUP BY D.ReportId)  C ON D.ReportID = C.ReportID AND S.ProcessedDate = C.UnExecutedAgreementRecdDate  
--                    WHERE     S.REPORTSTAT in ('PARTNERS_P_REPORTSTAT_40','PARTNERS_P_REPORTSTAT_41') ) UEAR ON D.ReportId = UEAR.ReportId  
  
  
WHERE     D.Inst_Code = 'PARTNERS' AND  
          D.REPORTCAT IN ('PARTNERS_P_REPORTCAT_48') AND   -- Progress Report/Annual Pre Award Doc  
		  S.REPORTSTAT IN ('PARTNERS_P_REPORTSTAT_43','PARTNERS_P_REPORTSTAT_45')--'PARTNERS_P_REPORTSTAT_44'- Completed 
  
GO
